package com.cc.dao.impl;

import com.cc.dao.BaseDao;
import com.cc.dao.EmployeeDao;
import com.cc.entity.Employee;
import com.cc.entity.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl extends BaseDao implements EmployeeDao {

    @Override
    public Page<Employee> EmployeeList(Page<Employee> page, String empName) {
        //存放分页数据
        List<Employee> employeesList = new ArrayList<>();
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT e.*,d.dept_name deptName ");
        sb.append(" FROM employee e,deptment d ");
        sb.append(" WHERE e.dep_id=d.id ");
        //sql指令
        if(empName!=null && !"".equals(empName)){
            sb.append(" AND e.emp_name LIKE CONCAT('%"+empName+"%') ");
        }
        sb.append(" ORDER BY e.id");
        sb.append(" LIMIT "+(page.getPageNum()-1)* page.getPageSize()+","+page.getPageSize()+" ");
        ResultSet rs =executeQuery(sb.toString());
        if (rs!=null){
            try {
                while (rs.next()){
                    Employee employee =new Employee();
                    employee.setId(rs.getInt("id"));
                    employee.setEmpName(rs.getString("emp_name"));
                    employee.setDepId(rs.getInt("dep_id"));
                    employee.setDeptName(rs.getString("deptName"));
                    employee.setPosition(rs.getString("position"));
                    employee.setStartDate(rs.getDate("start_date"));
                    employee.setPhone(rs.getString("phone"));
                    employee.setImgUrl(rs.getString("img_url"));
                    employeesList.add(employee);
                }
            }catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                closedAll(rs);
            }
        }
        page.setData(employeesList);
        page.setTotal(getEmployeeCount(empName));
        return page;
    }

    @Override
    public int getEmployeeCount(String empName) {
        int count=0;
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT COUNT(*) count ");
        sb.append(" FROM employee e,deptment d ");
        sb.append(" WHERE e.dep_id=d.id ");
        //sql指令
        if(empName!=null && !"".equals(empName)){
            sb.append(" AND e.emp_name LIKE CONCAT('%"+empName+"%') ");
        }
        ResultSet rs = executeQuery(sb.toString());
        if (rs != null) {
            try {
                while (rs.next()) {
                    count = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return count;
    }



    @Override
    public int det(int id) {
        String sql="DELETE FROM employee WHERE Id=?";
        return executeUpdate(sql,id);
    }

    @Override
    public int upd(Employee employee,int id) {
        String sql="UPDATE employee SET emp_name =?,dep_id=?,position =?,start_date =?,phone =?,img_url =? WHERE \n" +
                "  Id = ?;";
        return executeUpdate(sql,
                employee.getEmpName(),
                employee.getDepId(),
                employee.getPosition(),
                employee.getStartDate(),
                employee.getPhone(),
                employee.getImgUrl(),
                id
        );
    }

    @Override
    public int add(Employee employee) {
        String sql="INSERT INTO employee (emp_name, dep_id, position, start_date, phone, img_url) VALUES (?,?,?,?,?,?)";
        return executeUpdate(sql,
                employee.getEmpName(),
                employee.getDepId(),
                employee.getPosition(),
                employee.getStartDate(),
                employee.getPhone(),
                employee.getImgUrl()
                );
    }

    @Override
    public Employee editById(int id) {
        Employee employee =null;
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT e.*,d.dept_name deptName ");
        sb.append(" FROM employee e,deptment d ");
        sb.append(" WHERE e.dep_id=d.id and e.id=?");
        ResultSet rs= executeQuery(sb.toString(),id);
        try {
           if (rs.next()){
                employee =new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmpName(rs.getString("emp_name"));
                employee.setDepId(rs.getInt("dep_id"));
                employee.setDeptName(rs.getString("deptName"));
                employee.setPosition(rs.getString("position"));
                employee.setStartDate(rs.getDate("start_date"));
                employee.setPhone(rs.getString("phone"));
                employee.setImgUrl(rs.getString("img_url"));
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closedAll(rs);
        }
        return employee;
    }
}
